﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommunitySystemModel;
using System.Data;

namespace CommunitySystem.BO.SystemManager
{
    /// <summary>
    /// 实有房屋数据管理
    /// </summary>
   public static class SYFWManagerBO
   {
       #region 村居
       /// <summary>
       /// 获取村居列表
       /// </summary>
       /// <returns></returns>
       public static List<BAS_CJWXXModel> GetBAS_CJWXXModelList(int Id = 0)
       {
           List<BAS_CJWXXModel> list = new List<BAS_CJWXXModel>();

           string sql = string.Format(@"select * from BAS_CJWXX ");  //升序
           if (Id > 0)
               sql += string.Format(" where id={0}",Id);
           AccessHelper ah = new AccessHelper();
           var ds = ah.ExcuteDataSet(sql);
           if (ds == null || ds.Tables.Count < 1)
               return null;
           foreach (DataRow dr in ds.Tables[0].Rows)
           {
               BAS_CJWXXModel ac = new BAS_CJWXXModel()
               {
                   ID = Convert.ToInt32(dr["ID"].ToString()),
                   Name = dr["Name"] == DBNull.Value ? "" : dr["Name"].ToString(),
                   JWDM = dr["JWDM"] == DBNull.Value ? "" : dr["JWDM"].ToString(),
                   JWJJ = dr["JWJJ"] == DBNull.Value ? "" : dr["JWJJ"].ToString(),
                   ZMJ = dr["ZMJ"] == DBNull.Value ? "" : dr["ZMJ"].ToString(),
                   DZ = dr["DZ"] == DBNull.Value ? "" : dr["DZ"].ToString(),
                   YB = dr["YB"] == DBNull.Value ? "" : dr["YB"].ToString(),
                   QYFW = dr["QYFW"] == DBNull.Value ? "" : dr["QYFW"].ToString(),
                   SSSQ = dr["SSSQ"] == DBNull.Value ? "" : dr["SSSQ"].ToString(),
                   SSJZ = dr["SSJZ"] == DBNull.Value ? "" : dr["SSJZ"].ToString(),
                   LXDH = dr["LXDH"] == DBNull.Value ? "" : dr["LXDH"].ToString(),
                   DW = dr["DW"] == DBNull.Value ? "" : dr["DW"].ToString(),
                   BZ = dr["BZ"] == DBNull.Value ? "" : dr["BZ"].ToString()
               };
               list.Add(ac);
           }
           return list; 
       }
       /// <summary>
       /// 添加居委信息
       /// </summary>
       /// <param name="_BAS_CJWXXModel"></param>
       /// <returns></returns>
       public static int InsertBAS_CJWXXModel(BAS_CJWXXModel _BAS_CJWXXModel)
       {
           string sql = string.Format(@"INSERT INTO BAS_CJWXX
           ([Name]
           ,[JWJJ]
           ,[ZMJ]
           ,[DZ]
           ,[YB]
           ,[QYFW]
           ,[SSSQ]
           ,[SSJZ]
           ,[LXDH]
           ,[DW]
           ,[BZ]
           ,[JWDM])
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}')", _BAS_CJWXXModel.Name, _BAS_CJWXXModel.JWJJ, _BAS_CJWXXModel.ZMJ, _BAS_CJWXXModel.DZ, _BAS_CJWXXModel.YB, _BAS_CJWXXModel.QYFW, _BAS_CJWXXModel.SSSQ, _BAS_CJWXXModel.SSJZ, _BAS_CJWXXModel.LXDH, _BAS_CJWXXModel.DW, _BAS_CJWXXModel.BZ, _BAS_CJWXXModel.JWDM);
           AccessHelper ah = new AccessHelper();
           bool re = ah.ExcultData(sql);
           if (re)
               return 1;
           else
               return 0;
           
       }

       /// <summary>
       /// 修改居委信息
       /// </summary>
       /// <param name="_BAS_CJWXXModel"></param>
       /// <returns></returns>
       public static bool UpdateBAS_CJWXXModel(BAS_CJWXXModel _BAS_CJWXXModel)
       {
           string sql = string.Format(@"UPDATE BAS_CJWXX
       SET [Name] = '{0}'
          ,[JWJJ] = '{1}'
          ,[ZMJ] = '{2}'
          ,[DZ] = '{3}'
          ,[YB] = '{4}'
          ,[QYFW] = '{5}'
          ,[SSSQ] = '{6}'
          ,[SSJZ] = '{7}'
          ,[LXDH] = '{8}'
          ,[DW] ='{9}'
          ,[BZ] ='{10}'
          ,[JWDM] = '{11}'
     WHERE id={12}", _BAS_CJWXXModel.Name, _BAS_CJWXXModel.JWJJ, _BAS_CJWXXModel.ZMJ, _BAS_CJWXXModel.DZ, _BAS_CJWXXModel.YB, _BAS_CJWXXModel.QYFW, _BAS_CJWXXModel.SSSQ, _BAS_CJWXXModel.SSJZ, _BAS_CJWXXModel.LXDH, _BAS_CJWXXModel.DW, _BAS_CJWXXModel.BZ, _BAS_CJWXXModel.JWDM, _BAS_CJWXXModel.ID);
           AccessHelper ah = new AccessHelper();
           var re = ah.ExcultData(sql);
           return re ;

       }
       /// <summary>
       /// 删除村居委
       /// </summary>
       /// <param name="id"></param>
       /// <returns></returns>
       public static bool DeleteBAS_CJWXXModelById(int id)
       {
           string sql = string.Format(@"if not exists(select * from BAS_XQXX where CJID={0}) begin delete BAS_CJWXX where id={0} end", id);
           AccessHelper ah = new AccessHelper();
           var re = ah.ExcultData(sql);
           return re;

       }
       #endregion

       #region 小区
       public static List<BAS_XQXXModel> GeBAS_XQXXModelList(int Id = 0,int jwId=0)
       {
           List<BAS_XQXXModel> list = new List<BAS_XQXXModel>();

           string sql = string.Format(@"select * from BAS_XQXX ");
           if (Id > 0)
               sql += string.Format(" where id={0}", Id);

           if (jwId > 0)
           {
               sql = string.Format(@"select * from BAS_XQXX xq left join BAS_CJWXX cjw on xq.cjid=cjw.id where cjw.id={0}",jwId);
           }
           AccessHelper ah = new AccessHelper();
           var ds = ah.ExcuteDataSet(sql);
           if (ds == null || ds.Tables.Count < 1)
               return null;
           foreach (DataRow dr in ds.Tables[0].Rows)
           {
               BAS_XQXXModel ac = new BAS_XQXXModel()
               {
                   ID = Convert.ToInt32(dr["ID"].ToString()),
                   XQDZ = dr["XQDZ"] == DBNull.Value ? "" : dr["XQDZ"].ToString(),
                   XQName = dr["XQName"] == DBNull.Value ? "" : dr["XQName"].ToString(),
                   CJID = Convert.ToInt32(dr["CJID"].ToString()),
                   WYName = dr["WYName"] == DBNull.Value ? "" : dr["WYName"].ToString(),
                   WYTel = dr["WYTel"] == DBNull.Value ? "" : dr["WYTel"].ToString(),
                   WYOfficeDZ = dr["WYOfficeDZ"] == DBNull.Value ? "" : dr["WYOfficeDZ"].ToString(),
                   WYLinkMan = dr["WYLinkMan"] == DBNull.Value ? "" : dr["WYLinkMan"].ToString(),
                   ConstructionTime = dr["ConstructionTime"] == DBNull.Value ? "" : dr["ConstructionTime"].ToString(),
                   ConstructionArea = dr["ConstructionArea"] == DBNull.Value ? "" : dr["ConstructionArea"].ToString(),
                   GreenArea = dr["GreenArea"] == DBNull.Value ? "" : dr["GreenArea"].ToString(),
                   BuildingsNum = dr["BuildingsNum"] == DBNull.Value ? 0 : Convert.ToInt32(dr["BuildingsNum"].ToString()),
                   GatesNum = dr["GatesNum"] == DBNull.Value ? 0 : Convert.ToInt32(dr["GatesNum"].ToString()),
                   DoorsNum = dr["DoorsNum"] == DBNull.Value ? 0 : Convert.ToInt32(dr["DoorsNum"].ToString())

               };
               list.Add(ac);
           }
           return list;
       }

       public static int InsertBAS_XQXXModel(BAS_XQXXModel _BAS_XQXXModel)
       {
           string sql = string.Format(@"INSERT INTO BAS_XQXX
           ([XQDZ]
           ,[XQName]
           ,[CJID]
           ,[WYName]
           ,[WYTel]
           ,[WYOfficeDZ]
           ,[WYLinkMan]
           ,[ConstructionTime]
           ,[ConstructionArea]
           ,[GreenArea]
           ,[BuildingsNum]
           ,[GatesNum]
           ,[DoorsNum])
     VALUES
           ('{0}'
           ,'{1}'
           ,{2}
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,{10}
           ,{11}
           ,{12})", _BAS_XQXXModel.XQDZ, _BAS_XQXXModel.XQName, _BAS_XQXXModel.CJID,_BAS_XQXXModel.WYName, _BAS_XQXXModel.WYTel, _BAS_XQXXModel.WYOfficeDZ,_BAS_XQXXModel.WYLinkMan, _BAS_XQXXModel.ConstructionTime,_BAS_XQXXModel.ConstructionArea, _BAS_XQXXModel.GreenArea,  _BAS_XQXXModel.BuildingsNum, _BAS_XQXXModel.GatesNum, _BAS_XQXXModel.DoorsNum);
           AccessHelper ah = new AccessHelper();
           bool re = ah.ExcultData(sql);
           if (re)
               return 1;
           else
               return 0;

       }

       public static bool UpdateBAS_XQXXModel(BAS_XQXXModel _BAS_XQXXModel)
       {
           string sql = string.Format(@"UPDATE BAS_XQXX
   SET [XQDZ] = '{0}'
      ,[XQName] ='{1}'
      ,[CJID] = {2}
      ,[WYName] = '{3}'
      ,[WYTel] = '{4}'
      ,[WYOfficeDZ] = '{5}'
      ,[WYLinkMan] = '{6}'
      ,[ConstructionTime] = '{7}'
      ,[ConstructionArea] = '{8}'
      ,[GreenArea] = '{9}'
      ,[BuildingsNum] = {10}
      ,[GatesNum] = {11}
      ,[DoorsNum] = {12}
 WHERE id={13}", _BAS_XQXXModel.XQDZ, _BAS_XQXXModel.XQName, _BAS_XQXXModel.CJID, _BAS_XQXXModel.WYName, _BAS_XQXXModel.WYTel, _BAS_XQXXModel.WYOfficeDZ, _BAS_XQXXModel.WYLinkMan, _BAS_XQXXModel.ConstructionTime, _BAS_XQXXModel.ConstructionArea, _BAS_XQXXModel.GreenArea, _BAS_XQXXModel.BuildingsNum, _BAS_XQXXModel.GatesNum, _BAS_XQXXModel.DoorsNum,_BAS_XQXXModel.ID);
           AccessHelper ah = new AccessHelper();
           var re = ah.ExcultData(sql);
           return re;

       }

       public static bool DeleteBAS_XQXXModelById(int id)
       {
           string sql = string.Format(@"if not exists(select * from BAS_FWXX where XQID={0}) begin delete BAS_XQXX where id={0} end", id);
           AccessHelper ah = new AccessHelper();
           var re = ah.ExcultData(sql);
           return re;

       }
       #endregion

       #region 房屋信息
       public static bool AddInitHouseInfo(int xqId, int cens, int mds, int mdInits, string msEX,int hus, int huInits,string huEX)
        {
            string sql = string.Format(@"exec Proc_AddBAS_FWXX {0},{1},{2},{3},'{4}',{5},{6},'{7}'", xqId, cens, mds, mdInits, msEX, hus, huInits, huEX);
            AccessHelper ah = new AccessHelper();
            return ah.ExcultData(sql);
       }

       public static List<BAS_FWXXModel> GetBAS_FWXXModelByXqId(int xqId = 0)
       {
           List<BAS_FWXXModel> list = new List<BAS_FWXXModel>();

           string sql = string.Format(@"select aa.Hao,aa.HaoEx,SUM(aa.Rs) RenCount,SUM(aa.ShiCount) ShiCount,
                                    (select top 1 cen from BAS_FWXX mh1 where mh1.Hao=aa.Hao and mh1.HaoEx=aa.HaoEx  and mh1.XQID={0} order by Cen desc ) CenCount 
                                    from (
                                    select fw.Id,fw.Hao,fw.HaoEx,count(fw.Shi) ShiCount,(
                                    select COUNT(0) from BAS_RKXX where jzfwid=fw.Id
                                    )Rs from BAS_FWXX fw 
                                    left join BAS_XQXX as xq on xq.ID=fw.XQID where XQID={0}
                                    group by fw.Hao,fw.Id,fw.HaoEx
                                    ) aa 
                                    group by aa.Hao,aa.HaoEx order by CONVERT(int,aa.Hao)", xqId);
        
           #region
           //           string sql = string.Format(@"select aa.Hao,SUM(aa.Rs) RenCount,SUM(aa.ShiCount) ShiCount,
//                            (select top 1 cen from BAS_FWXX mh1 where mh1.Hao=aa.Hao and mh1.XQID={0} order by Cen desc ) CenCount 
//                            from (
//                            select distinct fw.Shi,fw.Hao,count(fw.Shi) ShiCount,(
//                            select COUNT(0) from BAS_RKXX where fwid=fw.Id
//                            )Rs from BAS_FWXX fw 
//                            left join BAS_XQXX as xq on xq.ID=fw.XQID where XQID={0}
//                            group by fw.Hao,fw.Id,fw.Shi
//                            ) aa 
           //                            group by aa.Hao order by CONVERT(int,aa.Hao)", xqId);
           #endregion
           AccessHelper ah = new AccessHelper();
           var ds = ah.ExcuteDataSet(sql);
           if (ds == null || ds.Tables.Count < 1)
               return null;
           foreach (DataRow dr in ds.Tables[0].Rows)
           {
               BAS_FWXXModel ac = new BAS_FWXXModel()
               {
                   Hao = dr["Hao"] == DBNull.Value ? 0 : Int32.Parse(dr["Hao"].ToString()),
                   HaoEx = dr["HaoEx"] == DBNull.Value ? "" : dr["HaoEx"].ToString(),
                   //ShiEx = dr["ShiEx"] == DBNull.Value ? "" : dr["ShiEx"].ToString(),
                   RenCount = dr["RenCount"] == DBNull.Value ? 0 : Int32.Parse(dr["RenCount"].ToString()),
                   ShiCount = dr["ShiCount"] == DBNull.Value ? 0 : Int32.Parse(dr["ShiCount"].ToString()),
                   CenCount = dr["CenCount"] == DBNull.Value ? 0 : Int32.Parse(dr["CenCount"].ToString())

               };
               list.Add(ac);
           }

           return list;
       }

       public static List<BAS_FWXXModel> GetBAS_FWXXModelByXqIdAndHao(string HaoEx, int xqId = 0, int Hao = 0)
       {
           List<BAS_FWXXModel> list = new List<BAS_FWXXModel>();

           string sql = string.Format(@"select fw.Id,fw.Hao,fw.HaoEx,fw.Cen,fw.Shi,fw.ShiEx,
                        (select COUNT(0) from BAS_RKXX where jzfwid=fw.Id)Rs
                        from BAS_FWXX fw
                        left join BAS_XQXX xq on xq.ID=fw.XQID 
                        where XQID={0} and Hao={1}and haoEx='{2}'
                                   group by fw.Hao,fw.HaoEx,fw.Id,fw.Shi,fw.ShiEx,fw.Cen order by fw.cen desc,fw.Shi", xqId, Hao, HaoEx);
        
           #region
//           string sql = string.Format(@"select distinct fw.Hao,fw.Cen,fw.Shi,
//(select COUNT(0) from BAS_RKXX where fwid=fw.Id)Rs
//from BAS_FWXX fw
//left join BAS_XQXX xq on xq.ID=fw.XQID 
//where XQID={0} and Hao={1}
//group by fw.Hao,fw.Id,fw.Shi,fw.Cen order by fw.cen desc,fw.Shi",xqId,Hao);
           #endregion
           AccessHelper ah = new AccessHelper();
           var ds = ah.ExcuteDataSet(sql);
           if (ds == null || ds.Tables.Count < 1)
               return null;
           foreach (DataRow dr in ds.Tables[0].Rows)
           {
               BAS_FWXXModel ac = new BAS_FWXXModel()
               {
                   Id = Int32.Parse(dr["Id"].ToString()),
                   Hao = dr["Hao"] == DBNull.Value ? 0 : Int32.Parse(dr["Hao"].ToString()),
                   HaoEx = dr["HaoEx"] == DBNull.Value ? "" : dr["HaoEx"].ToString(),
                   RenCount = dr["Rs"] == DBNull.Value ? 0 : Int32.Parse(dr["Rs"].ToString()),
                   Shi = dr["Shi"] == DBNull.Value ? 0 : Int32.Parse(dr["Shi"].ToString()),
                   ShiEx = dr["ShiEx"] == DBNull.Value ? "" : dr["ShiEx"].ToString(),
                   Cen = dr["Cen"] == DBNull.Value ? 0 : Int32.Parse(dr["Cen"].ToString())
               };
               list.Add(ac);
           }

           return list;
       }

       public static int GetCenByxqIdandfwHao(int xqID, int fwHao)
       {
           string sql = string.Format("select distinct count(cen) cenCount from BAS_FWXX where XQID={0} and Hao={1} group by cen",xqID,fwHao);
           AccessHelper ah = new AccessHelper();
           var ds = ah.GetDataSet(sql);
           return Convert.ToInt32(ds.Rows[0]["cenCount"].ToString());
       }

       public static bool AddFWInfo(List<BAS_FWXXModel> addFW)
       {
           string sql = "";
           foreach (BAS_FWXXModel item in addFW)
           {
               sql += string.Format(@"  INSERT INTO BAS_FWXX
           ([Hao]
           ,[HaoEx]
           ,[Shi]
           ,[ShiEx]
           ,[XQID]
           ,[Cen])
     VALUES
           ({0}
           ,'{1}'
           ,{2}
           ,'{3}'
           ,{4}
           ,{5})", item.Hao,item.HaoEx, item.Shi,item.ShiEx, item.XQID, item.Cen);
           }

           AccessHelper ah = new AccessHelper();
           if (ah.ExecuteNonQuery(sql) > 0)
               return true;
           else
               return false;
       }

       public static bool DeleteFwByFwID(int fwId)
       {
         string sql = string.Format(@"if not exists(select * from BAS_RKXX where jzfwid={0}) begin delete BAS_FWXX where id={0} end", fwId);
           AccessHelper ah = new AccessHelper();
           var re = ah.ExcultData(sql);
           return re;
       }

       public static bool UpdateBAS_FWXXModel(BAS_FWXXModel _BAS_FWXXModel)
       {
           string sql = string.Format(@"UPDATE BAS_FWXX
   SET [Hao] = {0}
      ,[HaoEx] = '{1}'
      ,[Shi] = {2}
      ,[ShiEx] = '{3}'
 WHERE id={4}", _BAS_FWXXModel.Hao, _BAS_FWXXModel.HaoEx, _BAS_FWXXModel.Shi, _BAS_FWXXModel.ShiEx, _BAS_FWXXModel.Id);
           AccessHelper ah = new AccessHelper();
           var re = ah.ExcultData(sql);
           return re;

       }
       #endregion

       #region 块区管理
      
       public static List<SimpleObject<int, string, int,string, bool>> GetXqAndHaoInfo(int kqId = 0,int CJWID=0)
       {
           List<SimpleObject<int, string, int,string, bool>> cuRe = new List<SimpleObject<int, string, int,string,bool>>();
           string sql = string.Format(@"select distinct xq.ID as XQID,xq.XQDZ,fw.Hao,fw.HaoEX,
                    (case when mhk.Hao is null then 0 else 1 end) isChecked from BAS_FWXX as fw 
                    left join BAS_KQHGX as mhk on mhk.hao=fw.Hao and mhk.haoEx=fw.HaoEx and fw.XQID=mhk.XQID 
                    left join BAS_XQXX as xq on fw.XQID=xq.ID
                    left join dbo.BAS_CJWXX cjw on cjw.id=xq.CJID
                    where mhk.hao is null");
          
           if (CJWID != 0)
           {
               sql += " and cjw.id=" + CJWID.ToString();
           }
           if (kqId != 0)
           {
               sql += " or mhk.KQID=" + kqId.ToString();
           }
           sql += " order by isChecked desc,xq.ID";
           AccessHelper ah = new AccessHelper();
           var ds = ah.ExcuteDataSet(sql);
           if (ds == null || ds.Tables.Count < 1)
               return null;
           foreach (DataRow dr in ds.Tables[0].Rows)
           {
               var cuO = new SimpleObject<int, string, int,string, bool>()
               {
                   Item1 = Int32.Parse(dr["XQID"].ToString()),
                   Item2 = dr["XQDZ"] == DBNull.Value ? "" : dr["XQDZ"].ToString(),
                   Item3 = Int32.Parse(dr["Hao"].ToString()),
                   Item4 = dr["HaoEX"] == DBNull.Value ? "" : dr["HaoEX"].ToString(),
                   Item5 = dr["isChecked"].ToString() == "1" ? true : false
               };
               cuRe.Add(cuO);
           }

           return cuRe;
       }


       public static List<BAS_KQXXModel> GetBAS_KQXXModelList(int Kqid=0,int CwId=0)
       {
           List<BAS_KQXXModel> list = new List<BAS_KQXXModel>();
           string sql = string.Empty;
           if (Kqid != 0)
           {
               sql = string.Format(@"select Id,KqName,LXDH,ZhuZ='',KqRenCount='',FWXQ='' from BAS_KQXX  where id={0}", Kqid);
           }
           else if (CwId!=0)
           {
               sql = string.Format(@"   select kq.Id,kq.KqName,kq.LXDH,
            (select top 1 kqry.XM from BAS_KQRY kqry 
            left join BAS_KQRYGX kqrygx on kqrygx.KQRYID=kqry.id
            where kqrygx.kqid=kq.id
            and kqrygx.juese='组长') ZhuZ,
            (select count(0) from BAS_KQRY kqry
            left join BAS_KQRYGX kqrygx on kqrygx.KQRYID=kqry.id
            where kqrygx.kqid=kq.id) KqRenCount,
            ( select * from (
				SELECT 
			   produce= STUFF
			   (
			   (SELECT DISTINCT ' , ' + (xq.XQDZ+kqhgx.HaoEx+ convert(varchar, kqhgx.Hao)+'号')
			   from BAS_XQXX xq
				left join BAS_KQHGX kqhgx on kqhgx.xqid=xq.id
				where kqhgx.kqid=kq.id and xq.id=a.id for xml path('')) , 1 , 1 , ''
			   ) from BAS_XQXX a
			   ) cc
				where cc.produce is not null ) FWXQ
            from BAS_KQXX kq
            where kq.JWID={0}
             ", CwId);
           }
           else if (Kqid == 0 && CwId == 0)
           {
               sql = string.Format(@"select Id,KqName,LXDH,ZhuZ='',KqRenCount='',FWXQ='' from BAS_KQXX");
           }

           AccessHelper ah = new AccessHelper();
           var ds = ah.ExcuteDataSet(sql);
           if (ds == null || ds.Tables.Count < 1)
               return null;
           foreach (DataRow dr in ds.Tables[0].Rows)
           {
               var _BAS_KQXXModel = new BAS_KQXXModel()
               {
                   Id = Int32.Parse(dr["Id"].ToString()),
                   KqName = dr["KqName"] == DBNull.Value ? "" : dr["KqName"].ToString(),
                   LXDH = dr["LXDH"] == DBNull.Value ? "" : dr["LXDH"].ToString(),
                   ZhuZ = dr["ZhuZ"] == DBNull.Value ? "" : dr["ZhuZ"].ToString(),
                   KQRenCount = dr["KQRenCount"] == DBNull.Value ? 0 : Convert.ToInt32(dr["KQRenCount"].ToString() == "" ? "0" : dr["KQRenCount"].ToString()),
                   FWXQ = dr["FWXQ"] == DBNull.Value ? "" : dr["FWXQ"].ToString() == "" ? "" : dr["FWXQ"].ToString().Substring(1).ToString(),
               };
               list.Add(_BAS_KQXXModel);
           }

           return list;
       }

       public static bool AddKQInfo(BAS_KQXXModel _BAS_KQXXModel)
       {
           string sqlKQ = string.Empty;
           string KQHGX = string.Empty;
           int result=0;
           sqlKQ = string.Format(@"INSERT INTO BAS_KQXX
                                   ([KqName]
                                   ,[JWID]
                                   ,[LXDH])
                             VALUES
                                   ('{0}'
                                   ,{1}
                                   ,'{2}')  SELECT  @@IDENTITY  as  Id", _BAS_KQXXModel.KqName, _BAS_KQXXModel.JWID, _BAS_KQXXModel.LXDH);
           AccessHelper ah = new AccessHelper();
         var returnID=Convert.ToInt32(ah.ExecuteScalar(sqlKQ));
         var HGX = _BAS_KQXXModel.CuHaoKqRelations;
         if (HGX != null && HGX.Count > 0)
         {
             foreach (var item in HGX)
             {
                 KQHGX += string.Format(@"INSERT INTO BAS_KQHGX
                               ([XQID]
                               ,[Hao]
                               ,[KQID]
                               ,[HaoEx])
                         VALUES
                               ({0}
                               ,{1}
                               ,{2}
                               ,'{3}')  ", item.Item1, item.Item3, returnID, item.Item4);
             }
             result = ah.ExecuteNonQuery(KQHGX);

         }
         else
             result = returnID;
           return result>0?true:false;
       }

       public static bool UpdateKQInfo(BAS_KQXXModel _BAS_KQXXModel)
       {
           string sqlKQ = string.Empty;
           string KQHGX = string.Empty;
           int result = 0;
           sqlKQ = string.Format(@"UPDATE BAS_KQXX
                           SET [KqName] = '{0}'
                              ,[JWID] ={1}
                              ,[LXDH] = '{2}'
                         WHERE id={3}", _BAS_KQXXModel.KqName, _BAS_KQXXModel.JWID, _BAS_KQXXModel.LXDH, _BAS_KQXXModel.Id);
           AccessHelper ah = new AccessHelper();
          int resultUpdate= ah.ExecuteNonQuery(sqlKQ);

           int SelKqId = _BAS_KQXXModel.Id;
           ah.ExecuteNonQuery(string.Format(@"DELETE FROM BAS_KQHGX
      WHERE KQID={0}",SelKqId));



           var HGX = _BAS_KQXXModel.CuHaoKqRelations;
           if (HGX != null && HGX.Count > 0)
           {
               foreach (var item in HGX)
               {
                   KQHGX += string.Format(@"INSERT INTO BAS_KQHGX
                               ([XQID]
                               ,[Hao]
                               ,[KQID]
                               ,[HaoEx])
                         VALUES
                               ({0}
                               ,{1}
                               ,{2}
                               ,'{3}')  ", item.Item1, item.Item3, SelKqId, item.Item4);
               }
               result = ah.ExecuteNonQuery(KQHGX);

           }
           else
               result = resultUpdate;
           return result > 0 ? true : false;
       }

       public static bool DeleteKQByID(int kqId)
       {
           string sql = string.Format(@" delete from BAS_KQHGX where kqid={0}
            delete from dbo.BAS_KQRYGX where kqid={0}
            delete from BAS_KQXX where id={0}", kqId);
           AccessHelper ah = new AccessHelper();
           var re = ah.ExcultData(sql);
           return re;
       }



       public static List<BAS_KQRY> GetBAS_KQRYList(int KQId=0, int kqRYId=0)
       {
           List<BAS_KQRY> list = new List<BAS_KQRY>();
           string sql =string.Format(@"
                select kqry.Id,kqry.XM,kqry.LXFS,kqry.SFZH,kqry.XB,kq.KqName, kqrygx.KQID,kqrygx.JueSe,kqry.GangWei from BAS_KQRY kqry
                left join BAS_KQRYGX kqrygx on kqry.Id=kqrygx.KQRYID
                left join BAS_KQXX kq on kq.id=kqrygx.KQID
                where 1=1 and kq.id is not null");
           if (KQId != 0)
               sql += string.Format(@" and kqrygx.KQID={0}", KQId);

           if (kqRYId != 0)
               sql = string.Format(@"select kqry.Id,kqry.XM,kqry.LXFS,kqry.SFZH,kqry.GangWei,kqry.XB,KqName='',
                kqrygx.id JsId,kqrygx.KQID,kqrygx.KQRYID,kqrygx.JueSe from BAS_KQRY kqry
                left join BAS_KQRYGX kqrygx on kqry.Id=kqrygx.KQRYID
                where kqry.id={0}", kqRYId);

           AccessHelper ah = new AccessHelper();
           var ds = ah.ExcuteDataSet(sql);
           if (ds == null || ds.Tables.Count < 1)
               return null;
           foreach (DataRow dr in ds.Tables[0].Rows)
           {
               var _BAS_KQRY = new BAS_KQRY()
               {
                   Id = Convert.ToInt32(dr["Id"].ToString()),
                   XM = dr["XM"] == DBNull.Value ? "" : dr["XM"].ToString(),
                   LXFS = dr["LXFS"] == DBNull.Value ? "" : dr["LXFS"].ToString(),
                   SFZH = dr["SFZH"] == DBNull.Value ? "" : dr["SFZH"].ToString(),
                   XB = dr["XB"] == DBNull.Value ? "" : dr["XB"].ToString(),
                   KqName = dr["KqName"] == DBNull.Value ? "" : dr["KqName"].ToString(),
                   KqId = Convert.ToInt32(dr["KQID"].ToString()),
                   JueSe = dr["JueSe"] == DBNull.Value ? "" : dr["JueSe"].ToString(),
                   GangWei = dr["GangWei"] == DBNull.Value ? "" : dr["GangWei"].ToString(),

                   CuBAS_KQRYGX=new List<BAS_KQRYGX>()
               };
               if (kqRYId != 0)
               {
                   foreach (DataRow item in ds.Tables[0].Rows)
                   {
                       var _BAS_KQRYGX = new BAS_KQRYGX() {
                           Id = Convert.ToInt32(item["JsId"].ToString()),
                           KQID = item["KQID"] == DBNull.Value ? 0 : Int32.Parse(item["KQID"].ToString()),
                           JueSe = item["JueSe"] == DBNull.Value ? "" : item["JueSe"].ToString(),
                           KQRYID = item["KQRYID"] == DBNull.Value ? 0 : Int32.Parse(item["KQRYID"].ToString())
                       };
                       _BAS_KQRY.CuBAS_KQRYGX.Add(_BAS_KQRYGX);
                   }
               }
               list.Add(_BAS_KQRY);
           }
           return list;
       }

       public static bool AddKQRy(BAS_KQRY _BAS_KQRY)
       {
           int result = 0;
           
           string sqlRy = string.Format(@"INSERT INTO BAS_KQRY
           ([XM]
           ,[SFZH]
           ,[XB]
           ,[LXFS]
           ,[GangWei])
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}') SELECT  @@IDENTITY  as  Id", _BAS_KQRY.XM, _BAS_KQRY.SFZH, _BAS_KQRY.XB, _BAS_KQRY.LXFS, _BAS_KQRY.GangWei);
           AccessHelper ah = new AccessHelper();
           var returnID = Convert.ToInt32(ah.ExecuteScalar(sqlRy));
           var RYGX = _BAS_KQRY.CuBAS_KQRYGX;
           if (RYGX != null && RYGX.Count > 0)
           {
               string sqlRYGX = string.Empty;
               foreach (var item in RYGX)
               {
                   sqlRYGX += string.Format(@"INSERT INTO BAS_KQRYGX
                           ([KQID]
                           ,[KQRYID]
                           ,[JueSe]
                           ,[GangWei])
                     VALUES
                           ({0}
                           ,{1}
                           ,'{2}'
                           ,'{3}') ", item.KQID, returnID, item.JueSe, item.GangWei);
               }
               result = ah.ExecuteNonQuery(sqlRYGX);
           }
           else
               result = returnID;


        return result>0?true:false;
       }

       public static bool UpdateKQRy(BAS_KQRY _BAS_KQRY)
       {
           int result = 0;

           string sqlRy = string.Format(@"UPDATE BAS_KQRY
                   SET [XM] = '{0}'
                      ,[SFZH] = '{1}'
                      ,[XB] ='{2}'
                      ,[LXFS] = '{3}'
                      ,[GangWei] ='{4}'
                 WHERE id={5}", _BAS_KQRY.XM, _BAS_KQRY.SFZH, _BAS_KQRY.XB, _BAS_KQRY.LXFS, _BAS_KQRY.GangWei, _BAS_KQRY.Id);
           AccessHelper ah = new AccessHelper();
           int resultUpdate = ah.ExecuteNonQuery(sqlRy);

           int SelEYId = _BAS_KQRY.Id;
           ah.ExecuteNonQuery(string.Format(@"DELETE FROM BAS_KQRYGX
      WHERE KQRYID={0}", SelEYId));

           var RYGX = _BAS_KQRY.CuBAS_KQRYGX;
           if (RYGX != null && RYGX.Count > 0)
           {
               string sqlRYGX = string.Empty;
               foreach (var item in RYGX)
               {
                   sqlRYGX += string.Format(@"INSERT INTO BAS_KQRYGX
                           ([KQID]
                           ,[KQRYID]
                           ,[JueSe]
                           ,[GangWei])
                     VALUES
                           ({0}
                           ,{1}
                           ,'{2}'
                           ,'{3}') ", item.KQID, SelEYId, item.JueSe, item.GangWei);
               }
               result = ah.ExecuteNonQuery(sqlRYGX);
           }
           else
               result = resultUpdate;


           return result > 0 ? true : false;
       }

       public static bool DeleteKQRyById(int id)
       {
           string sql = string.Format(@"delete from dbo.BAS_KQRYGX where KQRYID={0} 
                    delete from BAS_KQRY where ID ={0}", id);
           AccessHelper ah = new AccessHelper();
           var re = ah.ExcultData(sql);
           return re;
       }

       #endregion

       #region 组团服务
       public static List<string> GetBAS_RKXXInfo()
       {
           List<string> list = new List<string>();
           string sql = string.Format(@"select info=(XM+','+isnull(ZJHM,' ')+','+isnull(SG,' ')+','+isnull(JZDXZ,' ') )
                from BAS_RKXX where XM<>'' and XM is not null");
           
           AccessHelper ah = new AccessHelper();
           var ds = ah.ExcuteDataSet(sql);
           if (ds == null || ds.Tables.Count < 1)
               return null;
           foreach (DataRow dr in ds.Tables[0].Rows)
           {
               var cuO = dr["info"].ToString();
               list.Add(cuO);
           }

           return list;
       }

       public static List<MQ_XQCZModel> GetMQ_XQCZModelByCondition(DateTime? zfStarDate, DateTime? zfEndDate,
          DateTime? djStarDate, DateTime? djEndDate, string zfdx = "",
          List<string> lb = null, string djrName = "", string zjhm = "", string jjqks = "", string jzdz = "",int Id=0)
       {
           List<MQ_XQCZModel> list = new List<MQ_XQCZModel>();
           string sql = "";

           sql = string.Format(@"select * from dbo.MQ_XQCZ where 1=1 ");

           if (Id != 0)
               sql += string.Format(" and id={0}",Id);
           if (zfStarDate.HasValue)
               sql += string.Format(" and FYRQ>='{0}'",zfStarDate.Value.ToString());
           if (zfEndDate.HasValue)
               sql += string.Format(" and convert(datetime,convert(varchar(10),FYRQ,120))<='{0}'", zfEndDate.Value.ToString());

           if (djStarDate.HasValue)
               sql +=string.Format(" and InputTime<='{0}'" , djStarDate.Value.ToString());

           if (djEndDate.HasValue)
           {
               sql +=string.Format(" and convert(datetime,convert(varchar(10),InputTime,120))<='{0}'" ,djEndDate.Value.ToString());
           }

           if (!string.IsNullOrEmpty(zfdx))
           {
               sql += string.Format(" and DSRXM like '%{0}%'" , zfdx );
           }
           if (lb != null && lb.Count > 0)
           {
               sql += "and (";
               foreach (var item in lb)
               {
                   sql += "CHARINDEX('" + item + "',FWXQLB,0)>0 or ";
               }
               sql = sql.Substring(0, sql.Length - 3) + ")";
           }


           if (!string.IsNullOrEmpty(djrName))
           {
               sql += string.Format(" and FYRXM like '%{0}%'", djrName);
           }

           if (!string.IsNullOrEmpty(zjhm))
           {
               sql += string.Format(" and DSRZJHM like '%{0}%'", zjhm);
           }
           if (!string.IsNullOrEmpty(jjqks))
           {
               sql += string.Format(" and CHARINDEX(JJQK,'{0}',0)>0 ",jjqks);
           }

           if (!string.IsNullOrEmpty(jzdz))
           {
               sql +=  string.Format(" and md.DSRDZ='{0}'",jzdz);
           }
           sql += " order by FYRQ desc,ID desc ";
           AccessHelper ah = new AccessHelper();
           var ds = ah.ExcuteDataSet(sql);
           if (ds == null || ds.Tables.Count < 1)
               return null;

           foreach (DataRow dr in ds.Tables[0].Rows)
           {
               MQ_XQCZModel ac = new MQ_XQCZModel()
               {

                   //ID = Int32.Parse(dr["ID"].ToString()),
                   //DSRXM = dr["DSRXM"] == DBNull.Value ? "" : dr["DSRXM"].ToString(),
                   //InputTime = (dr["InputTime"] == DBNull.Value || string.IsNullOrEmpty(dr["InputTime"].ToString())) ? (DateTime?)null : DateTime.Parse(dr["InputTime"].ToString()),
                   //FYRQ = (dr["FYRQ"] == DBNull.Value || string.IsNullOrEmpty(dr["FYRQ"].ToString())) ? (DateTime?)null : DateTime.Parse(dr["FYRQ"].ToString()),
                   //FYRXM = dr["FYRXM"] == DBNull.Value ? "" : dr["FYRXM"].ToString(),
                   //  FWXQLB = dr["FWXQLB"] == DBNull.Value ? "" : dr["FWXQLB"].ToString(),
                   // JJQK = dr["JJQK"] == DBNull.Value ? "" : dr["JJQK"].ToString(),
                  // XQNR = dr["XQNR"] == DBNull.Value ? "" : dr["XQNR"].ToString()

                   ID = Int32.Parse(dr["ID"].ToString()),
                   BJRQ = (dr["BJRQ"] == DBNull.Value || string.IsNullOrEmpty(dr["BJRQ"].ToString())) ? (DateTime?)null : DateTime.Parse(dr["BJRQ"].ToString()),
                   BZ = dr["BZ"] == DBNull.Value ? "" : dr["BZ"].ToString(),
                   CJBLYJ = dr["CJBLYJ"] == DBNull.Value ? "" : dr["CJBLYJ"].ToString(),
                   CJSLR = dr["CJSLR"] == DBNull.Value ? "" : dr["CJSLR"].ToString(),
                   CJSLRID = (dr["CJSLRID"] == DBNull.Value || string.IsNullOrEmpty(dr["CJSLRID"].ToString())) ? 0 : Int32.Parse(dr["CJSLRID"].ToString()),
                   CJSLRQ = (dr["CJSLRQ"] == DBNull.Value || string.IsNullOrEmpty(dr["CJSLRQ"].ToString())) ? (DateTime?)null : DateTime.Parse(dr["CJSLRQ"].ToString()),
                   CZBH = dr["CZBH"] == DBNull.Value ? "" : dr["CZBH"].ToString(),
                   DSRXM = dr["DSRXM"] == DBNull.Value ? "" : dr["DSRXM"].ToString(),
                   DSRZJHM = dr["DSRZJHM"] == DBNull.Value ? "" : dr["DSRZJHM"].ToString(),
                   DSRDH = dr["DSRDH"] == DBNull.Value ? "" : dr["DSRDH"].ToString(),
                   DSRDZ = dr["DSRDZ"] == DBNull.Value ? "" : dr["DSRDZ"].ToString(),
                   FYRDH = dr["FYRDH"] == DBNull.Value ? "" : dr["FYRDH"].ToString(),
                 FYRQ = (dr["FYRQ"] == DBNull.Value || string.IsNullOrEmpty(dr["FYRQ"].ToString())) ? (DateTime?)null : DateTime.Parse(dr["FYRQ"].ToString()),
                   FYRXM = dr["FYRXM"] == DBNull.Value ? "" : dr["FYRXM"].ToString(),
                   FYRZJHM = dr["FYRZJHM"] == DBNull.Value ? "" : dr["FYRZJHM"].ToString(),
                   Grade = dr["Grade"] == DBNull.Value ? "" : dr["Grade"].ToString(),
                   HXJJJY = dr["HXJJJY"] == DBNull.Value ? "" : dr["HXJJJY"].ToString(),
                   InputTime = (dr["InputTime"] == DBNull.Value || string.IsNullOrEmpty(dr["InputTime"].ToString())) ? (DateTime?)null : DateTime.Parse(dr["InputTime"].ToString()),
                   Kind = dr["Kind"] == DBNull.Value ? "" : dr["Kind"].ToString(),
                   KQBLYJ = dr["KQBLYJ"] == DBNull.Value ? "" : dr["KQBLYJ"].ToString(),
                   KQSLR = dr["KQSLR"] == DBNull.Value ? "" : dr["KQSLR"].ToString(),
                   LinkID = (dr["LinkID"] == DBNull.Value || string.IsNullOrEmpty(dr["LinkID"].ToString())) ? 0 : Int32.Parse(dr["LinkID"].ToString()),
                   LSQK = dr["LSQK"] == DBNull.Value ? "" : dr["LSQK"].ToString(),
                  Source = dr["Source"] == DBNull.Value ? "" : dr["Source"].ToString(),
                   SQTCFK = dr["SQTCFK"] == DBNull.Value ? "" : dr["SQTCFK"].ToString(),
                   SSCJ = dr["SSCJ"] == DBNull.Value ? "" : dr["SSCJ"].ToString(),
                   SSQK = dr["SSQK"] == DBNull.Value ? "" : dr["SSQK"].ToString(),
                   Status = (dr["Status"] == DBNull.Value || string.IsNullOrEmpty(dr["Status"].ToString())) ? 0 : Int32.Parse(dr["Status"].ToString()),
                   UpdateTime = (dr["UpdateTime"] == DBNull.Value || string.IsNullOrEmpty(dr["UpdateTime"].ToString())) ? (DateTime?)null : DateTime.Parse(dr["UpdateTime"].ToString()),
                   XQBZ = dr["XQBZ"] == DBNull.Value ? "" : dr["XQBZ"].ToString(),
                   XQDZ = dr["XQDZ"] == DBNull.Value ? "" : dr["XQDZ"].ToString(),
                   XQNR = dr["XQNR"] == DBNull.Value ? "" : dr["XQNR"].ToString(),
                   XQZT = dr["XQZT"] == DBNull.Value ? "" : dr["XQZT"].ToString(),
                   ZTCFZR = dr["ZTCFZR"] == DBNull.Value ? "" : dr["ZTCFZR"].ToString(),
                   ZTCFZRID = dr["ZTCFZRID"] == DBNull.Value ? 0 :Convert.ToInt32(dr["ZTCFZRID"].ToString()),
                   ZTCSLRQ = (dr["ZTCSLRQ"] == DBNull.Value || string.IsNullOrEmpty(dr["ZTCSLRQ"].ToString())) ? (DateTime?)null : DateTime.Parse(dr["ZTCSLRQ"].ToString()),
                   ZTCYJ = dr["ZTCYJ"] == DBNull.Value ? "" : dr["ZTCYJ"].ToString(),
                 
                   FWXQLB = dr["FWXQLB"] == DBNull.Value ? "" : dr["FWXQLB"].ToString(),
                   JJQK = dr["JJQK"] == DBNull.Value ? "" : dr["JJQK"].ToString(),
                   ZFXS = dr["ZFXS"] == DBNull.Value ? "" : dr["ZFXS"].ToString(),
                   HOUSEID = dr["HOUSEID"] == DBNull.Value ? 0 : Int32.Parse(dr["HOUSEID"].ToString())

               };
               list.Add(ac);
           }
           return list;
       }
       /// <summary>
       /// 身份证号姓名获取人员详细信息
       /// </summary>
       /// <param name="sfzh"></param>
       /// <param name="name"></param>
       /// <returns></returns>
       public static BAS_RKXXModel GetBAS_RKXXModelByID(string sfzh, string name)
       {
           BAS_RKXXModel cuBsRkxx = new BAS_RKXXModel();
           string sql = string.Format("select * from [BAS_RKXX] where 1=1");

           if (!string.IsNullOrEmpty(sfzh))
               sql += " and zjhm='" + sfzh + "'";
           else
               sql += " and (zjhm is null or zjhm='') and XM='" + name + "'";

           AccessHelper ah = new AccessHelper();
           var ds = ah.GetDataSet(sql);
           if (ds != null && ds.Rows.Count > 0)
           {
               DataRow dr = ds.Rows[0];

               cuBsRkxx.ID = Int32.Parse(dr["ID"].ToString());
               cuBsRkxx.BYZK = dr["BYZK"] == DBNull.Value ? "" : dr["BYZK"].ToString();
               cuBsRkxx.BZ = dr["BZ"] == DBNull.Value ? "" : dr["BZ"].ToString();
               cuBsRkxx.CSRQ = dr["CSRQ"] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr["CSRQ"].ToString());
               cuBsRkxx.CYM = dr["CYM"] == DBNull.Value ? "" : dr["CYM"].ToString();
               cuBsRkxx.GJ = dr["GJ"] == DBNull.Value ? "" : dr["GJ"].ToString();

               cuBsRkxx.XB = dr["XB"] == DBNull.Value ? 0 : Int32.Parse(dr["XB"].ToString());
               cuBsRkxx.XM = dr["XM"] == DBNull.Value ? "" : dr["XM"].ToString();
               cuBsRkxx.ZJHM = dr["ZJHM"] == DBNull.Value ? "" : dr["ZJHM"].ToString();
               cuBsRkxx.ZZMM = dr["ZZMM"] == DBNull.Value ? "" : dr["ZZMM"].ToString();
               cuBsRkxx.CSD = dr["CSD"] == DBNull.Value ? "" : dr["CSD"].ToString();
               cuBsRkxx.GZDW = dr["GZDW"] == DBNull.Value ? "" : dr["GZDW"].ToString();
               cuBsRkxx.LXFS = dr["LXFS"] == DBNull.Value ? "" : dr["LXFS"].ToString();
               cuBsRkxx.XZ = dr["XZ"] == DBNull.Value ? "" : dr["XZ"].ToString();
               cuBsRkxx.WHCD = dr["WHCD"] == DBNull.Value ? "" : dr["WHCD"].ToString();
               cuBsRkxx.YHZGX = dr["YHZGX"] == DBNull.Value ? "" : dr["YHZGX"].ToString();
               cuBsRkxx.RKLB = dr["RKLB"] == DBNull.Value ? 0 : Int32.Parse(dr["RKLB"].ToString());
               cuBsRkxx.JZDXZ = dr["JZDXZ"] == DBNull.Value ? "" : dr["JZDXZ"].ToString();
               cuBsRkxx.HJDXZ = dr["HJDXZ"] == DBNull.Value ? "" : dr["HJDXZ"].ToString();
               
               cuBsRkxx.FwId = dr["JZFWID"] == DBNull.Value ? 0 : Int32.Parse(dr["JZFWID"].ToString());

           }
           return cuBsRkxx;
       }

       /// <summary>
       /// 获取块区村居受理人
       /// </summary>
       /// <param name="sfzh"></param>
       /// <param name="dshXm"></param>
       /// <returns></returns>
       public static BAS_KQXXModel GetMQDAKQViewDisposalCondition(int RKId = 0, string sfzh = "", string dshXm = "")
       {
           string sql = @"select distinct mk.* from BAS_KQXX as mk
            left join BAS_KQHGX as mkhr on mk.Id=mkhr.KQID            
            left join BAS_FWXX as mh on mkhr.hao=mh.Hao and  mkhr.haoex=mh.Haoex and mh.XQID=mkhr.XQID
            left join BAS_RKXX as ma on ma.JZFWID=mh.Id    
            where 1=1";
           if (RKId != 0)
               sql += string.Format(" and ma.id={0}", RKId);
            if (!string.IsNullOrEmpty(sfzh))
                sql += " and zjhm='" + sfzh + "'";
            else
                sql += " and (zjhm is null or zjhm='') and XM='" + dshXm + "'";
            AccessHelper da = new AccessHelper();
           var ds = da.ExcuteDataSet(sql);
           if (ds == null || ds.Tables.Count < 1 || ds.Tables[0].Rows.Count < 1)
               return null;
           DataRow dr = ds.Tables[0].Rows[0];
           BAS_KQXXModel acs = new BAS_KQXXModel()
           {
               Id = dr["Id"] == DBNull.Value ? 0 : Int32.Parse(dr["Id"].ToString()),
               KqName = dr["KqName"] == DBNull.Value ? "" : dr["KqName"].ToString(),
               JWID = dr["JWID"] == DBNull.Value ? 0 : Int32.Parse(dr["JWID"].ToString()),
               KQSLR = new List<string>()
           };
           if (acs.Id == 0)
               return null;
           sql = string.Format(@"select distinct XM from BAS_KQRY as mk 
            left join BAS_KQRYGX as mkr on mk.Id=mkr.KQRYID            
            where KQID={0} ", acs.Id);
           ds = da.ExcuteDataSet(sql);

           if (ds == null || ds.Tables.Count < 1)
               return acs;
           var kqslr = new List<string>();
           //foreach (DataRow dr1 in ds.Tables[0].Rows)
           //{
           //    kqslr.Add(dr1[0].ToString());
           //}
           for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
           {
               kqslr.Add(ds.Tables[0].Rows[i]["XM"].ToString());
           }
           acs.KQSLR = kqslr;

//           sql = string.Format(@"select distinct XM from BAS_KQRY as mk                         
//                                    left join DataDictionaryItem as dic on CHARINDEX(dic.DictCode,mk.gangwei,0)>0  and parentid=33
//                                    where dic.dictPinyin='cjslr'
//                                    ");
           sql = string.Format(@"select distinct XM,kq.JWID from BAS_KQRY as mk   
left join BAS_KQRYGX as mkr on mk.Id=mkr.KQRYID 
left join  BAS_KQXX kq on kq.id= mkr.KQID               
left join DataDictionaryItem as dic on CHARINDEX(dic.DictCode,mk.gangwei,0)>0  and parentid=33
where dic.dictPinyin='cjslr' and kq.JWID={0}", acs.JWID);
           ds = da.ExcuteDataSet(sql);

           if (ds == null || ds.Tables.Count < 1)
               return acs;
           var cjslr = new List<string>();
           foreach (DataRow dr1 in ds.Tables[0].Rows)
           {
               cjslr.Add(dr1[0].ToString());
           }
           acs.CjSLR = cjslr;

           return acs;
       }

       public static int UpdateMQ_XQCZModel(MQ_XQCZModel adis)
       {
           int resu = 0;
           string sql = string.Format(@"Update MQ_XQCZ set [LinkID]={0},[Source]='{1}',[Kind]='{2}',[Grade]='{3}',
                   [SSQK]='{4}',[SSCJ]='{5}',[FYRXM]='{6}',[FYRZJHM]='{7}',[FYRDH]='{8}',[FYRQ]={9},[DSRXM]='{10}',
                   [DSRZJHM]='{11}',[XQDZ]='{12}',[XQZT]='{13}',[XQNR]='{14}',[XQBZ]='{15}',[CZBH]='{16}',[KQSLR]='{17}',
                   [KQBLYJ]='{18}',[CJSLRID]='{19}', [CJSLR]='{20}',[CJSLRQ]={21},[CJBLYJ]='{22}',[ZTCFZRID]='{23}',
                   [ZTCFZR]='{24}',[ZTCYJ]='{25}',[SQTCFK]='{26}',[HXJJJY]='{27}',[BZ]='{28}',
                   [LSQK]='{29}',[BJRQ]={30},[Status]={31},
                    [InputTime]={32},[UpdateTime]={33},
                   ZTCSLRQ={34},
                FWXQLB='{35}',JJQK='{36}',ZFXS='{37}',HouseId={38},DSRDH='{39}',DSRDZ='{40}'
                    where id={41} ",
           adis.LinkID, adis.Source, adis.Kind, adis.Grade, adis.SSQK, adis.SSCJ, adis.FYRXM, adis.FYRZJHM, adis.FYRDH,
           (adis.FYRQ.HasValue ? (adis.FYRQ.Value.Year == 1900 ? "NULL" : "'" + adis.FYRQ.Value.ToString("yyyy-MM-dd")) + "'" : "NULL"),
    adis.DSRXM, adis.DSRZJHM, adis.XQDZ, adis.XQZT, adis.XQNR, adis.XQBZ, adis.CZBH, adis.KQSLR, adis.KQBLYJ, adis.CJSLRID,
    adis.CJSLR,
     (adis.CJSLRQ.HasValue ? (adis.CJSLRQ.Value.Year == 1900 ? "NULL" : "'" + adis.CJSLRQ.Value.ToString("yyyy-MM-dd")) + "'" : "NULL"),
   adis.CJBLYJ, adis.ZTCFZRID, adis.ZTCFZR, adis.ZTCYJ, adis.SQTCFK, adis.HXJJJY, adis.BZ, adis.LSQK,
   (adis.BJRQ.HasValue ? (adis.BJRQ.Value.Year == 1900 ? "NULL" : "'" + adis.BJRQ.Value.ToString("yyyy-MM-dd")) + "'" : "NULL"),
   adis.Status,
     (adis.InputTime.HasValue ? (adis.InputTime.Value.Year == 1900 ? "NULL" : "'" + adis.InputTime.Value.ToString("yyyy-MM-dd")) + "'" : "NULL"),
       (adis.UpdateTime.HasValue ? (adis.UpdateTime.Value.Year == 1900 ? "NULL" : "'" + adis.UpdateTime.Value.ToString("yyyy-MM-dd")) + "'" : "NULL"),
       (adis.ZTCSLRQ.HasValue ? (adis.ZTCSLRQ.Value.Year == 1900 ? "NULL" : "'" + adis.ZTCSLRQ.Value.ToString("yyyy-MM-dd")) + "'" : "NULL"),
    adis.FWXQLB, adis.JJQK, adis.ZFXS, adis.HOUSEID, adis.DSRDH,adis.DSRDZ, adis.ID);
           AccessHelper da = new AccessHelper();
           resu = da.ExecuteNonQuery(sql);
           return resu;
       }

       public static int InsertMQ_XQCZModel(MQ_XQCZModel adis)
       {
           if (adis == null)
               return 0;

           int resu = 0;
           string sql = string.Format(@"INSERT INTO [MQ_XQCZ]
           ([LinkID],[Source],[Kind],[Grade],[SSQK],[SSCJ],[FYRXM],[FYRZJHM],[FYRDH],[FYRQ],
            [DSRXM],[DSRZJHM],[XQDZ],[XQZT],[XQNR],[XQBZ],[CZBH],[KQSLR],[KQBLYJ],[CJSLRID],[CJSLR],[CJSLRQ],
           [CJBLYJ],[ZTCFZRID],[ZTCFZR],[ZTCYJ],[SQTCFK],[HXJJJY],[BZ],[LSQK],[BJRQ],[Status],
           [InputTime],[UpdateTime], ZTCSLRQ,
                FWXQLB,JJQK,ZFXS,HouseId,DSRDH,DSRDZ) 
           VALUES ({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',{9},'{10}','{11}','{12}','{13}',
           '{14}','{15}','{16}','{17}','{18}',{19},'{20}',{21},'{22}','{23}','{24}','{25}','{26}','{27}','{28}',
           '{29}',{30},'{31}',
{32},{33},{34},'{35}','{36}','{37}',{38},'{39}','{40}') SELECT  @@IDENTITY as Id",
            adis.LinkID, adis.Source, adis.Kind, adis.Grade, adis.SSQK, adis.SSCJ, adis.FYRXM, adis.FYRZJHM, adis.FYRDH,
            (adis.FYRQ.HasValue ? (adis.FYRQ.Value.Year == 1900 ? "NULL" : "'" + adis.FYRQ.Value.ToString("yyyy-MM-dd")) + "'" : "NULL"),
            adis.DSRXM, adis.DSRZJHM, adis.XQDZ, adis.XQZT, adis.XQNR, adis.XQBZ, adis.CZBH, adis.KQSLR, adis.KQBLYJ, adis.CJSLRID,
            adis.CJSLR,
            (adis.CJSLRQ.HasValue ? (adis.CJSLRQ.Value.Year == 1900 ? "NULL" : "'" + adis.CJSLRQ.Value.ToString("yyyy-MM-dd")) + "'" : "NULL"),
             adis.CJBLYJ, adis.ZTCFZRID, adis.ZTCFZR, adis.ZTCYJ, adis.SQTCFK, adis.HXJJJY, adis.BZ, adis.LSQK,
             (adis.BJRQ.HasValue ? (adis.BJRQ.Value.Year == 1900 ? "NULL" : "'" + adis.BJRQ.Value.ToString("yyyy-MM-dd")) + "'" : "NULL"),
           adis.Status, 
            (adis.InputTime.HasValue ? (adis.InputTime.Value.Year == 1900 ? "NULL" : "'" + adis.InputTime.Value.ToString("yyyy-MM-dd")) + "'" : "NULL"),
             (adis.UpdateTime.HasValue ? (adis.UpdateTime.Value.Year == 1900 ? "NULL" : "'" + adis.UpdateTime.Value.ToString("yyyy-MM-dd")) + "'" : "NULL"),
           (adis.ZTCSLRQ.HasValue ? (adis.ZTCSLRQ.Value.Year == 1900 ? "NULL" : "'" + adis.ZTCSLRQ.Value.ToString("yyyy-MM-dd")) + "'" : "NULL"),
            adis.FWXQLB, adis.JJQK, adis.ZFXS, adis.HOUSEID,adis.DSRDH,adis.DSRDZ);
           
           AccessHelper da = new AccessHelper();
           resu =Convert.ToInt32(da.ExecuteScalar(sql));
           return resu;

       }


       public static int DeleteMQ_XQCZModelById(int id)
       {
           int resu = 0;
           string sql = string.Format(@"delete from MQ_XQCZ where ID={0}", id);
           AccessHelper da = new AccessHelper();
           resu = da.ExecuteNonQuery(sql);
           return resu;
       }

       #endregion
   }


}
